===테이블 삭제=============================================================

drop table history;
drop sequence history_no_seq;
drop table workorder;
drop sequence workorder_no_seq;
drop table nextstep;
drop table product;
drop sequence lot_no_seq;
drop table condition;
drop table station;
drop table inventory;
drop sequence inventory_no_seq;
drop table material;
drop table emp;
drop sequence emp_no_seq;

===테이블 생성==============================================================

create sequence emp_no_seq nocache;
create table emp(
	emp_no number primary key,
	emp_name varchar2(500),
	password varchar2(500),
	grade varchar2(500)
);

create table material(
	material_name varchar2(500) primary key
);

create sequence inventory_no_seq nocache;
create table inventory(
	inventory_no number primary key,
	inventory_date date,
	material_name varchar2(500),
	maker varchar2(500),
	input_quantity number,
	output_quantity number,
	constraint fk_m_name foreign key(material_name) references material on delete cascade
);

create table station(
	station_name varchar2(500) primary key,
	material_name varchar2(500),
	constraint fk_material_name foreign key(material_name) references material on delete cascade
);

create table condition(
	condition_name varchar2(500) primary key,
	material_name varchar2(500),
	constraint fk_materials_name foreign key(material_name) references material on delete cascade
);

create table nextstep(
	station_name varchar2(500),
	condition_name varchar2(500),
	next_station varchar2(500),
	constraint fk_s_name foreign key(station_name) references station on delete cascade,
	constraint fk_c_name foreign key(condition_name) references condition on delete cascade
);

create sequence lot_no_seq nocache;
create table product(
	lot_no number primary key,
	station_name varchar2(500),
	condition_name varchar2(500),
	constraint fk_sp_name foreign key(station_name) references station on delete cascade,
	constraint fk_cp_name foreign key(condition_name) references condition on delete cascade
);

create sequence workorder_no_seq nocache;
create table workorder(
	workorder_no number primary key,
	workorder_date date,
	workorder_quantity number,
	complete_quantity number
);

create sequence history_no_seq nocache;
create table history(
	history_no number primary key,
	workorder_no number,
	lot_no number,
	station_name varchar2(500),
	condition_name varchar2(500),
	emp_no number,
	history_date date,
	inventory_no number,
	constraint fk_workorder_no foreign key(workorder_no) references workorder on delete cascade,
	constraint fk_station_name foreign key(station_name) references station on delete cascade,
	constraint fk_condition_name foreign key(condition_name) references condition on delete cascade,
	constraint fk_emp_no foreign key(emp_no) references emp on delete cascade,
	constraint fk_inventory_no foreign key(inventory_no) references inventory on delete cascade
);


===테이블 필수 내용 insert====================================================

insert into emp values(1, '김사장', 1234, '사장');

insert into material values('배추');
insert into material values('양념');
insert into material values('소금');
insert into material values('박스');

insert into station values('세척','배추');
insert into station values('절이기','소금');
insert into station values('속넣기','양념');
insert into station values('포장','박스');

insert into condition (condition_name) values('대기');
insert into condition values('배추불량', '배추');
insert into condition values('소금불량', '소금');
insert into condition values('양념불량', '양념');
insert into condition values('포장훼손', '박스');
insert into condition (condition_name) values('완료');

insert into NEXTSTEP values('세척', '완료', '절이기');
insert into NEXTSTEP values('절이기', '완료', '속넣기');
insert into NEXTSTEP values('속넣기', '완료', '포장');


===========모든 테이블 불러오기(select)=========================================

select * from material;
select * from inventory;
select * from workorder;
select * from emp;
select * from station;
select * from product;
select * from condition;
select * from nextstep;
select * from history;


===기타================================================================================
select * from emp
insert into inventory values (inventory_no_seq.nextval,sysdate,'배추','용인','20','10');
insert into workorder values (workorder_no_seq.nextval,sysdate,20,10);
insert into product values (lot_no_seq.nextval,'절임','완료');
insert into history values (history_no_seq.nextval,'1','1','세척','완료','1',sysdate,'1');

select * from (select ceil(rownum/6) as page,count(*),hd
from (select to_char(history_date,'yyyy-mm-dd') as hd from history 
where condition_name='파손') group by hd order by hd desc) where page=1

select to_char(history_date,'yyyy-mm-dd') as hd from history 
where condition_name='파손'


SELECT no,title,writer,view_count,write_date,ref,restep,relevel
		FROM(SELECT no,title,writer,view_count,write_date,CEIL(rownum/5) AS page,ref,restep,relevel
		FROM(SELECT no,title,writer,view_count,to_char(write_date,'YYYY.MM.DD') as
		write_date,ref,restep,relevel FROM spring_board_reply order by ref desc,restep asc)
		) WHERE page=#value#


insert into emp values('1','1',)

select * from (
select count(*),hd,ceil(rownum/6)
from (select to_char(history_date,'yyyy-mm-dd') as hd from history 
where condition_name='파손') group by hd order by hd desc

select station_name,count(*) from history where his_date=sysdate group by station_name


select * from history


